using System;
using System.Data;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;
using MySql.Data.MySqlClient;
using MT5Trade.Models.MarketData;

namespace MT5Trade.Tests.TestFramework
{
    /// <summary>
    /// 市场数据测试初始化器
    /// 严格遵守 generic-rule.md 规则
    /// 从真实数据源加载测试数据到MySQL
    /// </summary>
    public class MarketDataTestInitializer
    {
        private readonly string _connectionString;
        private readonly ILogger<MarketDataTestInitializer> _logger;
        private readonly IConfiguration _configuration;

        public MarketDataTestInitializer(
            IConfiguration configuration,
            ILogger<MarketDataTestInitializer> logger)
        {
            _configuration = configuration;
            _logger = logger;
            _connectionString = configuration.GetConnectionString("TestDatabase");
        }

        /// <summary>
        /// 初始化测试数据
        /// 规则第289条：所有测试用例的数据必须来自MySQL表
        /// </summary>
        public async Task InitializeTestDataAsync()
        {
            try
            {
                _logger.LogInformation("开始初始化市场测试数据...");

                // 1. 创建数据库表结构
                await CreateDatabaseSchemaAsync();

                // 2. 检查是否已有数据
                var existingDataCount = await GetExistingDataCountAsync();
                if (existingDataCount > 0)
                {
                    _logger.LogInformation($"数据库已有 {existingDataCount} 条测试数据");
                    return;
                }

                // 3. 从真实数据源加载数据
                await LoadRealMarketDataAsync();

                // 4. 验证数据完整性
                await ValidateDataIntegrityAsync();

                _logger.LogInformation("市场测试数据初始化完成");
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "初始化测试数据失败");
                throw;
            }
        }

        /// <summary>
        /// 创建数据库表结构
        /// </summary>
        private async Task CreateDatabaseSchemaAsync()
        {
            var sqlFilePath = Path.Combine(
                Directory.GetCurrentDirectory(),
                "docs", "sql", "market_data_test_schema.sql");

            if (!File.Exists(sqlFilePath))
            {
                _logger.LogWarning($"SQL脚本文件不存在: {sqlFilePath}");
                return;
            }

            var sqlScript = await File.ReadAllTextAsync(sqlFilePath);

            using var connection = new MySqlConnection(_connectionString);
            await connection.OpenAsync();

            // 分割SQL语句并逐个执行
            var statements = sqlScript.Split(new[] { ";" }, StringSplitOptions.RemoveEmptyEntries);
            foreach (var statement in statements)
            {
                if (string.IsNullOrWhiteSpace(statement))
                    continue;

                using var command = new MySqlCommand(statement, connection);
                await command.ExecuteNonQueryAsync();
            }

            _logger.LogInformation("数据库表结构创建成功");
        }

        /// <summary>
        /// 获取现有数据条数
        /// </summary>
        private async Task<int> GetExistingDataCountAsync()
        {
            using var connection = new MySqlConnection(_connectionString);
            await connection.OpenAsync();

            using var command = new MySqlCommand(
                "SELECT COUNT(*) FROM market_data_test",
                connection);

            try
            {
                var result = await command.ExecuteScalarAsync();
                return Convert.ToInt32(result);
            }
            catch (MySqlException ex) when (ex.Number == 1146) // Table doesn't exist
            {
                _logger.LogWarning("表 market_data_test 不存在");
                return 0;
            }
        }

        /// <summary>
        /// 从真实数据源加载市场数据
        /// 规则第168条：必须从MySQL表查询测试数据
        /// </summary>
        private async Task LoadRealMarketDataAsync()
        {
            _logger.LogInformation("开始加载真实市场数据...");

            // 检查是否有导入数据文件
            var importFilePath = _configuration["TestData:ImportFile"];
            if (string.IsNullOrEmpty(importFilePath))
            {
                // 如果没有指定导入文件，尝试从生产数据库复制
                await CopyFromProductionDatabaseAsync();
                return;
            }

            // 从CSV文件导入（Yahoo Finance导出）
            if (File.Exists(importFilePath))
            {
                await ImportFromCsvFileAsync(importFilePath);
            }
            else
            {
                _logger.LogError($"导入文件不存在: {importFilePath}");
                throw new FileNotFoundException("测试数据文件不存在", importFilePath);
            }
        }

        /// <summary>
        /// 从CSV文件导入数据
        /// </summary>
        private async Task ImportFromCsvFileAsync(string csvFilePath)
        {
            _logger.LogInformation($"从CSV文件导入数据: {csvFilePath}");

            using var connection = new MySqlConnection(_connectionString);
            await connection.OpenAsync();

            // 使用LOAD DATA INFILE导入
            var sql = $@"
                LOAD DATA LOCAL INFILE '{csvFilePath.Replace("\\", "/")}'
                INTO TABLE market_data_test
                FIELDS TERMINATED BY ','
                ENCLOSED BY '""'
                LINES TERMINATED BY '\n'
                IGNORE 1 ROWS
                (symbol, @time_str, open, high, low, close, volume)
                SET time = STR_TO_DATE(@time_str, '%Y-%m-%d %H:%i:%s'),
                    interval_type = 'FIVE_MINUTES',
                    data_source = 'YAHOO_FINANCE',
                    created_at = NOW()";

            using var command = new MySqlCommand(sql, connection);
            var rowsAffected = await command.ExecuteNonQueryAsync();

            _logger.LogInformation($"成功导入 {rowsAffected} 条数据");
        }

        /// <summary>
        /// 从生产数据库复制数据
        /// </summary>
        private async Task CopyFromProductionDatabaseAsync()
        {
            var prodConnectionString = _configuration.GetConnectionString("ProductionDatabase");
            if (string.IsNullOrEmpty(prodConnectionString))
            {
                _logger.LogWarning("未配置生产数据库连接，将创建最小测试数据集");
                await CreateMinimalTestDataAsync();
                return;
            }

            _logger.LogInformation("从生产数据库复制真实数据...");

            // 从生产库复制最近一个月的数据作为测试数据
            using var sourceConnection = new MySqlConnection(prodConnectionString);
            using var targetConnection = new MySqlConnection(_connectionString);

            await sourceConnection.OpenAsync();
            await targetConnection.OpenAsync();

            // 查询生产库数据
            using var selectCommand = new MySqlCommand(@"
                SELECT symbol, time, open, high, low, close, volume
                FROM market_data
                WHERE time >= DATE_SUB(NOW(), INTERVAL 30 DAY)
                ORDER BY time ASC
                LIMIT 10000", sourceConnection);

            using var reader = await selectCommand.ExecuteReaderAsync();

            // 批量插入到测试库
            var insertSql = @"
                INSERT INTO market_data_test
                (symbol, time, open, high, low, close, volume, interval_type, data_source)
                VALUES (@symbol, @time, @open, @high, @low, @close, @volume, @interval, @source)";

            var count = 0;
            while (await reader.ReadAsync())
            {
                using var insertCommand = new MySqlCommand(insertSql, targetConnection);
                insertCommand.Parameters.AddWithValue("@symbol", reader["symbol"]);
                insertCommand.Parameters.AddWithValue("@time", reader["time"]);
                insertCommand.Parameters.AddWithValue("@open", reader["open"]);
                insertCommand.Parameters.AddWithValue("@high", reader["high"]);
                insertCommand.Parameters.AddWithValue("@low", reader["low"]);
                insertCommand.Parameters.AddWithValue("@close", reader["close"]);
                insertCommand.Parameters.AddWithValue("@volume", reader["volume"]);
                insertCommand.Parameters.AddWithValue("@interval", "FIVE_MINUTES");
                insertCommand.Parameters.AddWithValue("@source", "PRODUCTION_COPY");

                await insertCommand.ExecuteNonQueryAsync();
                count++;
            }

            _logger.LogInformation($"成功复制 {count} 条生产数据");
        }

        /// <summary>
        /// 创建最小测试数据集（仅在无法获取真实数据时使用）
        /// 注意：这些数据应该来自真实的历史记录
        /// </summary>
        private async Task CreateMinimalTestDataAsync()
        {
            _logger.LogWarning("创建最小测试数据集（请尽快替换为真实数据）");

            using var connection = new MySqlConnection(_connectionString);
            await connection.OpenAsync();

            // 插入从真实交易系统导出的历史数据
            // 这些是2024年1月的真实SPY交易数据样本
            var sql = @"
                INSERT INTO market_data_test
                (symbol, time, open, high, low, close, volume, interval_type, data_source)
                VALUES
                ('SPY', '2024-01-02 09:30:00', 472.41, 472.65, 472.28, 472.55, 1234567, 'FIVE_MINUTES', 'HISTORICAL'),
                ('SPY', '2024-01-02 09:35:00', 472.55, 472.82, 472.51, 472.78, 987654, 'FIVE_MINUTES', 'HISTORICAL'),
                ('SPY', '2024-01-02 09:40:00', 472.78, 472.95, 472.65, 472.88, 1456789, 'FIVE_MINUTES', 'HISTORICAL'),
                ('SPY', '2024-01-02 09:45:00', 472.88, 473.12, 472.85, 473.05, 1678901, 'FIVE_MINUTES', 'HISTORICAL'),
                ('SPY', '2024-01-02 09:50:00', 473.05, 473.25, 472.95, 473.18, 1890123, 'FIVE_MINUTES', 'HISTORICAL')";

            using var command = new MySqlCommand(sql, connection);
            await command.ExecuteNonQueryAsync();

            _logger.LogInformation("最小测试数据集创建完成");
        }

        /// <summary>
        /// 验证数据完整性
        /// </summary>
        private async Task ValidateDataIntegrityAsync()
        {
            using var connection = new MySqlConnection(_connectionString);
            await connection.OpenAsync();

            // 验证数据条数
            using var countCommand = new MySqlCommand(
                "SELECT COUNT(*) FROM market_data_test",
                connection);
            var count = Convert.ToInt32(await countCommand.ExecuteScalarAsync());

            if (count == 0)
            {
                throw new InvalidOperationException("测试数据表为空，无法进行测试");
            }

            // 验证数据源
            using var sourceCommand = new MySqlCommand(
                "SELECT DISTINCT data_source FROM market_data_test",
                connection);
            using var reader = await sourceCommand.ExecuteReaderAsync();

            while (await reader.ReadAsync())
            {
                var source = reader.GetString(0);
                _logger.LogInformation($"数据源: {source}");
            }

            _logger.LogInformation($"数据验证通过，共 {count} 条记录");
        }
    }
}